package ro.gateway.aida.usr;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.servlet.Constants;
import ro.gateway.aida.utils.SortedList;
import ro.gateway.aida.utils.Utils;


/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: ro-gateway</p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro; Mihai Postelnicu, mihai@ro-gateway.org
 * @version 1.0-* @version $Id: UserDB.java,v 1.1 2004/10/24 23:37:05 mihaipostelnicu Exp $
 */
public class UserDB extends DBPersistenceManager {
	private UserDB(PersistenceToken token) {
		super(token);
	}

	public static UserDB getManager(PersistenceToken token) {
		return new UserDB(token);
	}


	public void insert(User item)
		  throws SQLException {
		if (item.getId() == -1)
			item.setId(db_token.getIdgenerators().nextVal(Constants.IDGEN_USERS));

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT);

			ps.setLong(1, item.getId());
			ps.setString(2, item.getLogin());
			ps.setString(3, item.getPass());
			ps.setString(4, item.getFirstNames());
			ps.setString(5, item.getLastName());
			ps.setString(6, item.getEmail());
			ps.setString(7, item.getWorkingLanguage());
			ps.setInt(8, item.isStandby() ? 1 : 0);
			if (item.getOrganization() != null) {
				ps.setString(9, item.getOrganization());
			} else {
				ps.setNull(9, Types.VARCHAR);
			}
			if (item.getCountry_iso3() == null) {
				ps.setNull(10, Types.VARCHAR);
			} else {
				ps.setString(10, item.getCountry_iso3());
			}
			ps.executeUpdate();

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		UserGroupDB ugdb = UserGroupDB.getManager(token);
		int defaultGroup = Integer.parseInt(Utils.getProperty(token, "defaultGroup"));

		if (defaultGroup != 0)
			ugdb.addUser(defaultGroup, item.getId());
	}

	public void update(User item)
		  throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE);
			ps.setString(1, item.getLogin());
			ps.setString(2, item.getPass());
			ps.setString(3, item.getFirstNames());
			ps.setString(4, item.getLastName());
			ps.setString(5, item.getEmail());
			ps.setString(6, item.getWorkingLanguage());
			ps.setInt(7, item.isStandby() ? 1 : 0);
			if (item.getOrganization() != null) {
				ps.setString(8, item.getOrganization());
			} else {
				ps.setNull(8, Types.VARCHAR);
			}
			if (item.getCountry_iso3() == null) {
				ps.setNull(9, Types.VARCHAR);
			} else {
				ps.setString(9, item.getCountry_iso3());
			}
			ps.setLong(10, item.getId());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

	}

	public void delete(long id)
		  throws SQLException {
		Connection con = getConnection();

		removeUserFromGroups(id);

		try {
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

	}

	public User getByLogin(String login) throws SQLException {
		Connection con = getConnection();
		User result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_LOGIN);
			ps.setString(1, login.toUpperCase());

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new User();
				result.setId(rs.getLong(1));
				result.setPass(rs.getString(2));
				result.setFirstNames(rs.getString(3));
				result.setLastName(rs.getString(4));
				result.setEmail(rs.getString(5));
				result.setWorkingLanguage(rs.getString(6));
				result.setStandby(rs.getInt(7) == 1);
				result.setLogin(login);
				result.setOrganization(rs.getString(8));
				result.setCountry_iso3(rs.getString(9));
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public User getById(long id) throws SQLException {
		Connection con = getConnection();
		User result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setLong(1, id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new User();
				result.setId(id);
				result.setLogin(rs.getString(1));
				result.setPass(rs.getString(2));
				result.setFirstNames(rs.getString(3));
				result.setLastName(rs.getString(4));
				result.setEmail(rs.getString(5));
				result.setWorkingLanguage(rs.getString(6));
				result.setStandby(rs.getInt(7) == 1);
				result.setOrganization(rs.getString(8));
				result.setCountry_iso3(rs.getString(9));
				result.setCountryName(rs.getString(10));
			}

			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return result;
	}

	public boolean changePass(long user_id, String pass) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(CHANGE_PWD);
			ps.setString(1, pass);
			ps.setLong(2, user_id);

			ps.executeUpdate();
			ps.close();
		} finally {
			con.close();
		}

		return true;
	}


	public User getByEmail(String email) throws SQLException {
		Connection con = getConnection();
		User result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_EMAIL);
			ps.setString(1, email);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new User();
				result.setId(rs.getLong(8));
				result.setLogin(rs.getString(1));
				result.setPass(rs.getString(2));
				result.setFirstNames(rs.getString(3));
				result.setLastName(rs.getString(4));
				result.setEmail(rs.getString(5));
				result.setWorkingLanguage(rs.getString(6));
				result.setStandby(rs.getInt(7) == 1);
				result.setOrganization(rs.getString(8));
				result.setCountry_iso3(rs.getString(9));
			}

			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return result;
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public User[] getAllArray(String sort_criteria)
		  throws SQLException {
		Connection con = getConnection();
		User[] result = null;
		ArrayList items = new ArrayList();

		String q = GET_ALL;
		if (sort_criteria != null) {
			if (sort_criteria.startsWith("-")) {
				sort_criteria = sort_criteria.substring(1);
				q += " ORDER BY " + sort_criteria + " DESC";
			} else {
				q += " ORDER BY " + sort_criteria + " ASC";
			}
		}
		try {
			PreparedStatement ps = con.prepareStatement(q);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				User item = new User();

				item.setId(rs.getLong(1));
				item.setLogin(rs.getString(2));
				item.setPass(rs.getString(3));
				item.setFirstNames(rs.getString(4));
				item.setLastName(rs.getString(5));
				item.setEmail(rs.getString(6));
				item.setWorkingLanguage(rs.getString(7));
				item.setStandby(rs.getInt(8) == 1);
				item.setOrganization(rs.getString(9));
				item.setCountry_iso3(rs.getString(10));

				items.add(item);
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new User[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public SortedList getAll() throws SQLException {
		Connection con = getConnection();
		SortedList ret = new SortedList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				User item = new User();
				item.setId(rs.getLong(1));
				item.setLogin(rs.getString(2));
				item.setPass(rs.getString(3));
				item.setFirstNames(rs.getString(4));
				item.setLastName(rs.getString(5));
				item.setEmail(rs.getString(6));
				item.setWorkingLanguage(rs.getString(7));
				item.setStandby(rs.getInt(8) == 1);
				item.setOrganization(rs.getString(9));
				item.setCountry_iso3(rs.getString(10));
				item.setCountryName(rs.getString(11));
				ret.add(item);
			}

			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return ret;
	}

	public long getLastid()
		  throws SQLException {
		Connection con = getConnection();
		long id = 0;

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);
			ResultSet rs = ps.executeQuery();

			if (rs.next())
				id = rs.getLong(1);

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return id;
	}

	public long getOwner(long object_id, int object_type) throws SQLException {
		long ret = 0;
		PreparedStatement ps = null;
		Connection con = getConnection();

		try {
			if (object_type == Arbiter.PROJECT_TYPE)
				ps = con.prepareStatement(GET_PROJECT_OWNER);

			if (object_type == Arbiter.ORG_TYPE)
				ps = con.prepareStatement(GET_ORG_OWNER);

			if (object_type == Arbiter.PERSON_TYPE)
				ps = con.prepareStatement(GET_PERS_OWNER);

			ps.setLong(1, object_id);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
				ret = rs.getLong(1);
			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return ret;
	}

	public final void removeUserFromGroups(long user_id)
		  throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.
				  prepareStatement(UNLINK_USER_FROM_ALL);
			ps.setLong(1, user_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public String getPWDReset(long id) {
		Connection con = getConnection();
		String result = "N";

		try {
			PreparedStatement ps = con.prepareStatement("SELECT pwdreset FROM users WHERE id=?");
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getString(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
				sqlEx.printStackTrace();
			}
		}

		if (result != null)
			return result.toUpperCase();

		return null;
	}

	public void setPWDReset(String pwd_reset, long id) {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("UPDATE users SET pwdreset=? WHERE id=?");
			ps.setString(1, pwd_reset);
			ps.setLong(2, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
				sqlEx.printStackTrace();
			}
		}
	}

	private static final String GET_PERS_OWNER = "SELECT user_id FROM persons " +
		  "WHERE id=?";
	private static final String GET_ORG_OWNER = "SELECT user_id FROM orgs " +
		  "WHERE id=?";
	private static final String GET_PROJECT_OWNER = "SELECT user_id FROM objects " +
		  "WHERE activity_id=? AND obj_type=0";
	private static final String INSERT =
		  "INSERT INTO users (id, login, pass, first_names, last_name, email, wlang, standby, organization, country_iso3) " +
		  "VALUES (?,?,?,?,?,?,?,?,?,?)";
	private static final String UPDATE =
		  "UPDATE users SET login = ?, pass = ?, first_names = ?, " +
		  "last_name = ?, email = ?, wlang = ?, standby = ?, organization=?, country_iso3=? " + "WHERE id = ?";
	private static final String DELETE = "DELETE FROM users WHERE id = ?";
	private static final String GET_BY_LOGIN =
		  "SELECT id, pass, first_names, last_name, email, wlang, standby, organization, country_iso3 " +
		  "FROM users WHERE upper(login) = ?";
	private static final String GET_BY_ID =
		  "SELECT u.login, u.pass, u.first_names, u.last_name, u.email, u.wlang, u.standby, u.organization, u.country_iso3, c.name " +
		  "FROM users u LEFT OUTER JOIN countries_iso3 c ON (c.iso3=u.country_iso3) WHERE id = ?";
	private static final String GET_ALL =
		  "SELECT u.id, u.login, u.pass, u.first_names, u.last_name, u.email, u.wlang, " +
		  "u.standby, u.organization, u.country_iso3, c.name " +
		  "FROM users u LEFT OUTER JOIN countries_iso3 c ON (c.iso3=u.country_iso3) ";
	private static final String GET_LAST_ID = "SELECT max(id) FROM users";
	private static final String GET_BY_EMAIL =
		  "SELECT login, pass, first_names, last_name, email, wlang, standby,id,organization, country_iso3 " +
		  "FROM users WHERE email=?";
	private static final String CHANGE_PWD = "UPDATE users set pass=?,pwdreset='Y' WHERE id=?";
	private static final String UNLINK_USER_FROM_ALL = "DELETE FROM users_groups WHERE user_id=?";
}
